package intranet.models;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class SearchFilesModel {
	Connection DBConnection=null;
	public SearchFilesModel(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
	public ResultSet SearchFiles(int cat_id,String keyword){
		ResultSet result=null;
		String Sql="";
		try{
			Statement ConStatement=DBConnection.createStatement();
	if(keyword==null&&cat_id!=0){
				Sql="SELECT UPLOADED_FILES.FILE_ID," +
						  "UPLOADED_FILES.FILE_NAME," +
						  "DBMS_LOB.SUBSTR(UPLOADED_FILES.FILE_DESC,DBMS_LOB.GETLENGTH(UPLOADED_FILES.FILE_DESC),1) AS FILE_DESC," +
						  "UPLOADED_FILES.FILE_SIZE," +
						  "UPLOADED_FILES.FREQUENCY" +
						  " FROM " +
						  "UPLOADED_FILES INNER JOIN FILE_CATEGORIES " +
						  "ON FILE_CATEGORIES.CATEGORY_ID=UPLOADED_FILES.CATEGORY_ID " +
						  "WHERE UPLOADED_FILES.CATEGORY_ID="+cat_id+"";	
		result=ConStatement.executeQuery(Sql);
				}
	else if(keyword!=null&&cat_id==0){
		Sql="SELECT UPLOADED_FILES.FILE_ID," +
				  "UPLOADED_FILES.FILE_NAME," +
				  "DBMS_LOB.SUBSTR(UPLOADED_FILES.FILE_DESC,DBMS_LOB.GETLENGTH(UPLOADED_FILES.FILE_DESC),1) AS FILE_DESC," +
				  "UPLOADED_FILES.FILE_SIZE," +
				  "UPLOADED_FILES.FREQUENCY" +
				  " FROM " +
				  "UPLOADED_FILES INNER JOIN FILE_KEYWORDS " +
				  "ON UPLOADED_FILES.FILE_ID=FILE_KEYWORDS.FILE_ID " +
				  "WHERE lower(UPLOADED_FILES.FILE_TITLE) LIKE '%"+keyword.toLowerCase()+"%' OR lower(FILE_KEYWORDS.KEYWORD) LIKE'%"+keyword.toLowerCase()+"%'";
result=ConStatement.executeQuery(Sql);
		}
	else if(keyword!=null&&cat_id!=0){
			Sql="SELECT UPLOADED_FILES.FILE_ID," +
					  "UPLOADED_FILES.FILE_NAME," +
					  "DBMS_LOB.SUBSTR(UPLOADED_FILES.FILE_DESC,DBMS_LOB.GETLENGTH(UPLOADED_FILES.FILE_DESC),1) AS FILE_DESC," +
					  "UPLOADED_FILES.FILE_SIZE," +
					  "UPLOADED_FILES.FREQUENCY" +
					  " FROM " +
					  "UPLOADED_FILES INNER JOIN FILE_CATEGORIES " +
					  "ON FILE_CATEGORIES.CATEGORY_ID=UPLOADED_FILES.CATEGORY_ID " +
					  "INNER JOIN FILE_KEYWORDS " +
					  "ON UPLOADED_FILES.FILE_ID=FILE_KEYWORDS.FILE_ID " +
					  "WHERE UPLOADED_FILES.CATEGORY_ID="+cat_id+" AND lower(UPLOADED_FILES.FILE_TITLE) LIKE '%"+keyword.toLowerCase()+"%' OR lower(FILE_KEYWORDS.KEYWORD) LIKE'%"+keyword.toLowerCase()+"%'";	
		result=ConStatement.executeQuery(Sql);
		}
	else{
		Sql="SELECT FILE_ID," +
				  "FILE_NAME," +
				  "DBMS_LOB.SUBSTR(FILE_DESC,DBMS_LOB.GETLENGTH(FILE_DESC),1) AS FILE_DESC," +
				  "FILE_SIZE," +
				  "FREQUENCY FROM UPLOADED_FILES";
		result=ConStatement.executeQuery(Sql);
	}
				}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
}
